package fxification.test;

import junit.framework.TestCase;
import fxification.db.Sql;
import junit.framework.Assert;
import javax.sql.DataSource;
import junit.textui.TestRunner;
import javafx.data.Pair;
import fxification.java.FxSql;
import fxification.db.DataSet;
import java.sql.Statement;
import java.sql.ResultSet;

public class SqlTest extends TestCase {
    def DB_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    def DB_URL = "jdbc:derby:FxSQL";

    public function testCreateDirectConnection():Void {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};create=true"};

        Assert.assertNotNull(sql);
        Assert.assertNotNull(sql.connection);

        sql.connection.close();
    }

    public function testCreateConnectionWithProperties():Void {
        def sql = Sql{
            url:"{DB_URL}"
            properties: [Pair{name:"user" value:"sa"},Pair{name:"password" value:"admin"}]
        };

        Assert.assertNotNull(sql);
        Assert.assertNotNull(sql.connection);

        sql.connection.close();
    }


    public function testCreateDataSourceConnection():Void {
        var dataSource:DataSource = SimpleDataSource{
            username:"sa"
            password:"admin"
            url:DB_URL
        };

        def sql = Sql{dataSource:dataSource};

        Assert.assertNotNull(sql);
        Assert.assertNotNull(sql.connection);

        sql.connection.close();
    }

    public function testCreateDestroyTable() {
        var conn = FxSql.getConnection(null, DB_URL, "sa", "admin", null);
        SqlTestHelper.detroyTable(conn);
        SqlTestHelper.createTables(conn);
        SqlTestHelper.populateData(conn);
        SqlTestHelper.detroyTable(conn);
    }

    public function testSimpleTestQuery() {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"
            sql:"select * from TEST_TBL"};

        SqlTestHelper.createTables(sql.connection);

        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);

        var count = 0;
        sql.onDataSetAvailable = function(ds:DataSet){
            count = sizeof ds.getRows();
        }

        sql.query();

        Assert.assertEquals (3, count);

         SqlTestHelper.detroyTable(sql.connection);
    }

    public function testStartingQueryEvent() {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"
            sql:"select firstname, lastname, phone from TEST_TBL"
            onStartingQuery:function(stmt:Statement) {
                Assert.assertNotNull(stmt);
            }
        };
        SqlTestHelper.createTables(sql.connection);
        sql.query();
        SqlTestHelper.detroyTable(sql.connection);
    }

    public function testDataSetAvailableEvent() {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"
            sql:"select * from TEST_TBL"
            onDataSetAvailable:function(ds:DataSet) {
                Assert.assertNotNull(ds);
                Assert.assertEquals("DataSetAvailable ", sizeof ds.getRows(), 3);
            }
        };
        SqlTestHelper.createTables(sql.connection);

        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);

        sql.query();

        SqlTestHelper.detroyTable(sql.connection);
    }

    public function testResultSetAvailableEvent() {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"
            sql:"select * from TEST_TBL"
            onResultSetAvailable:function(rs:ResultSet) {
                Assert.assertNotNull(rs);
                var count = 0;
                while (rs.next()){
                    count++;
                }

                Assert.assertEquals("onResultSetAvailable not return ResultSet", count, 3);
            }
        };
        SqlTestHelper.createTables(sql.connection);

        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);

        sql.query();

        SqlTestHelper.detroyTable(sql.connection);
    }


    public function testEachRowEvent() {
        var count = 0;
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"
            sql:"select * from TEST_TBL"
            onEachRow:function(dr:DataSet.Row) {
                Assert.assertNotNull(dr);
                count++;
                if(count >= 3){
                    Assert.assertEquals("", count , 3);
                }
            }
        };
        SqlTestHelper.createTables(sql.connection);

        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);

        sql.query();

        SqlTestHelper.detroyTable(sql.connection);
    }

    public function testInsertQuery() {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"};

        SqlTestHelper.createTables(sql.connection);
        Sql{
            connection:sql.connection
            sql:"insert into test_tbl (firstname,lastname,phone,email,title)"
            "values('vladimir','vivien','813333333', 'email@good.com','janitor')"
            onUpdateCompleted:function(rowsEffected:Integer){
                Assert.assertEquals("Rows not effected", rowsEffected, 1);
            }

        }.update();
        SqlTestHelper.detroyTable(sql.connection);
    }
    public function testUpdateQueryWithGeneratedKeys() {
        def sql = Sql{
            username:"sa"
            password:"admin"
            url:"{DB_URL};"};

        SqlTestHelper.createTables(sql.connection);
        SqlTestHelper.populateData(sql.connection);
        SqlTestHelper.populateData(sql.connection);
        Sql{
            connection:sql.connection
            sql:"insert into test_tbl (firstname,lastname,phone,email,title)"
            "values('vladimir','vivien','813333333', 'email@good.com','janitor')"
            onUpdateCompleted:function(rowsEffected:Integer){
                Assert.assertEquals("Rows not effected", rowsEffected, 1);
            }
            onDataSetAvailable:function(ds:DataSet){
                Assert.assertEquals("Generated key columns not returned", sizeof ds.getRows(), 1);
                Assert.assertEquals("Generated key columns not returned", ds.getRows()[0].getColumn(0).getValue() as Integer, 3);
            }
        }.update();

        SqlTestHelper.detroyTable(sql.connection);
    }

//    public function testAsyncOperation():Void {
//        var rowsEffected = 0;
//        def sql = Sql{username:"sa" password:"admin" url:"{DB_URL};"};
//        TaskRunner.runTask(
//            function(){
//                println ("starting task");
//                SqlTestHelper.createTables(sql.connection);
//                Sql{
//                    connection:sql.connection
//                    sql:"insert into test_tbl (firstname,lastname,phone,email,title)"
//                    "values('vladimir','vivien','813333333', 'email@good.com','janitor')"
//                    onUpdateCompleted:function(rows:Integer){
//                        rowsEffected = rows;
//                    }
//                }.update();
//            },
//            function() {
//                        println ("Rows Effected {rowsEffected}");
//                Assert.assertEquals("Async Test is not working for Sql", 1, 3);
//                SqlTestHelper.detroyTable(sql.connection);
//            }
//        );
//    }
}

public function run() {
    def test = TestRunner{};
    test.run(test.getTest(SqlTest.class.getName()));
}


